package 个人密码库.service;

import 个人密码库.model.Password;
import 个人密码库.util.Encryptor;
import 个人密码库.view.inputview.ExcelInput;
import 个人密码库.view.inputview.Input;
import 个人密码库.view.outputview.ConsoleOutput;
import 个人密码库.view.outputview.ExcelOutput;
import 个人密码库.view.outputview.Output;

import java.io.File;
import java.security.GeneralSecurityException;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 注意：如果将来这个类的逻辑过于复杂，可以把与数据库交互的部分分离出去，这一层专门处理用户请求和从数据层返回的数据(DAO模式)
 */
public class PasswordService {
    private final String JDBC_URL;
    private final String JDBC_USER;
    private final String JDBC_PASSWORD;
    private final String OUTPUT_PATH;
    private final String INPUT_PATH;
    private boolean shouldExportToExcel = false;    // 是否将结果输出到Excel表格

    public PasswordService(Map<String, String> config) {
        JDBC_URL = config.get("JDBC_URL");
        JDBC_USER = config.get("JDBC_USER");
        JDBC_PASSWORD = config.get("JDBC_PASSWORD");
        OUTPUT_PATH = config.get("OUTPUT_PATH");
        INPUT_PATH = config.get("INPUT_PATH");
    }

    public void setShouldExportToExcel(boolean shouldExportToExcel) {
        this.shouldExportToExcel = shouldExportToExcel;
    }

    // 将数据输出到指定Excel文件
    private void outputToExcel(List<Password> passwords, String path) {
        Output excelOutput = new ExcelOutput();
        excelOutput.output(passwords, path);
        shouldExportToExcel = false;    // 还原为默认的状态
        System.out.println("已成功导出为Excel表格！请到" + new File(OUTPUT_PATH).getAbsolutePath() + "中查看。");
    }

    // 将数据输出到命令行终端
    private void outputToConsole(List<Password> passwords) {
        Output consoleOutput = new ConsoleOutput();
        consoleOutput.output(passwords, null);
    }

    // 查询所有密码
    public void getAllPasswords() throws SQLException, GeneralSecurityException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT id, password, description, modification_date FROM passwords")) {
                try (ResultSet resultSet = preparedStatement.executeQuery()) {
                    List<Password> passwords = new ArrayList<>();
                    final String TARGET_PATH = OUTPUT_PATH + "all_passwords.xlsx";

                    while (resultSet.next()) {
                        Password password = new Password();

                        password.setId(resultSet.getInt("id"));
                        password.setPassword(Encryptor.decrypt(resultSet.getString("password")));
                        password.setDescription(resultSet.getString("description"));
                        password.setModificationDate(resultSet.getTimestamp("modification_date").toLocalDateTime());
                        // 注意：直接将所有数据库记录加载到内存中，此方法只适用于小数量级数据库，数量级太大可能会造成OOM！！！
                        // 如果数据太多，可以尝试循环每次取一部分出来的办法来进行优化。
                        passwords.add(password);
                    }
                    outputToConsole(passwords);
                    if (shouldExportToExcel) {
                        outputToExcel(passwords, TARGET_PATH);
                    }
                }
            }
        }
    }

    // 根据关键字模糊查询某个密码
    public void getSomePasswords(String keyWord) throws SQLException, GeneralSecurityException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement statement = connection.prepareStatement("SELECT id, password, description, modification_date FROM passwords WHERE description LIKE ?")) {
                statement.setString(1, "%" + keyWord + "%");
                try (ResultSet resultSet = statement.executeQuery()) {
                    List<Password> passwords = new ArrayList<>();
                    final String TARGET_PATH = OUTPUT_PATH + "some_passwords.xlsx";

                    while (resultSet.next()) {
                        Password password = new Password();

                        password.setId(resultSet.getInt("id"));
                        password.setPassword(Encryptor.decrypt(resultSet.getString("password")));
                        password.setDescription(resultSet.getString("description"));
                        password.setModificationDate(resultSet.getTimestamp("modification_date").toLocalDateTime());
                        passwords.add(password);
                    }
                    outputToConsole(passwords);
                    if (shouldExportToExcel) {
                        outputToExcel(passwords, TARGET_PATH);
                    }
                }
            }
        }
    }

    // 存储一个新密码
    public void storeOnePassword(Password password) throws SQLException, GeneralSecurityException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement statement = connection.prepareStatement("INSERT INTO passwords (password, description, modification_date) VALUES (?, ?, ?)", Statement.RETURN_GENERATED_KEYS)) {
                statement.setObject(1, Encryptor.encrypt(password.getPassword()));
                statement.setObject(2, password.getDescription());
                statement.setObject(3, LocalDateTime.now());
                statement.executeUpdate();
            }
        }
    }

    // （根据Excel文件）批量存储
    public void insertAllPasswords() {
        Input input = new ExcelInput(JDBC_URL, JDBC_USER, JDBC_PASSWORD, INPUT_PATH);
        input.input();
    }

    // （根据密码id）删除一个密码
    public void deleteOnePassword(int id) throws SQLException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement statement = connection.prepareStatement("DELETE FROM passwords WHERE id = ?")) {
                statement.setObject(1, id);
                int count = statement.executeUpdate();
                System.out.println(count + "条数据被删除。");
            }
        }
    }

    // （根据密码id列表）批量删除某些密码
    public void deleteSomePasswords(int[] ids) throws SQLException {
        StringBuilder sql = new StringBuilder("DELETE FROM passwords WHERE id IN (");
        for (int i = 0; i < ids.length; i++) {
            sql.append("?");
            if (i < ids.length - 1) {
                sql.append(", ");
            }
        }
        sql.append(");");

        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement preparedStatement = connection.prepareStatement(sql.toString())) {
                for (int i = 0; i < ids.length; i++) {
                    preparedStatement.setObject(i + 1, ids[i]);
                }
                int count = preparedStatement.executeUpdate();
                System.out.println(count + "条数据被删除。");
            }
        }
    }

    // 删除所有密码
    public void clear() throws SQLException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement statement = connection.prepareStatement("TRUNCATE TABLE passwords")) {
                int count = statement.executeUpdate();
                if (count == 0) {
                    System.out.println("数据已被清空！");
                }
            }
        }
    }

    // 修改一个密码并更新修改时间
    public void updatePassword(int id, String newPassword) throws SQLException, GeneralSecurityException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement statement = connection.prepareStatement("UPDATE passwords SET password = ?, modification_date = ? WHERE id = ?")) {
                statement.setObject(1, Encryptor.encrypt(newPassword));
                statement.setObject(2, LocalDateTime.now());
                statement.setObject(3, id);
                int count = statement.executeUpdate();
                System.out.println(count + "条数据被更改。");
            }
        }
    }

    // 修改一个密码的描述并更改修改时间
    public void updateDescription(int id, String newDescription) throws SQLException {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
            try (PreparedStatement statement = connection.prepareStatement("UPDATE passwords SET description = ?, modification_date = ? WHERE id = ?")) {
                statement.setObject(1, newDescription);
                statement.setObject(2, LocalDateTime.now());
                statement.setObject(3, id);
                int count = statement.executeUpdate();
                System.out.println(count + "条数据被更改。");
            }
        }
    }
}
